create database Bank

go

use Bank

go


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Inventory') and o.name = 'FK_INVENTOR_REFERENCE_PSIINVEN')
alter table Inventory
   drop constraint FK_INVENTOR_REFERENCE_PSIINVEN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PsiAccess') and o.name = 'FK_PSIACCES_REFERENCE_INVENTOR')
alter table PsiAccess
   drop constraint FK_PSIACCES_REFERENCE_INVENTOR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"PsiAfter-sales"') and o.name = 'FK_PSIAFTER_REFERENCE_PSISALES')
alter table "PsiAfter-sales"
   drop constraint FK_PSIAFTER_REFERENCE_PSISALES
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PsiInventory') and o.name = 'FK_PSIINVEN_REFERENCE_PSIINFOR')
alter table PsiInventory
   drop constraint FK_PSIINVEN_REFERENCE_PSIINFOR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PsiProcurement') and o.name = 'FK_PSIPROCU_REFERENCE_PSIINFOR')
alter table PsiProcurement
   drop constraint FK_PSIPROCU_REFERENCE_PSIINFOR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PsiProduct') and o.name = 'FK_PSIPRODU_REFERENCE_PSIINFOR')
alter table PsiProduct
   drop constraint FK_PSIPRODU_REFERENCE_PSIINFOR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PsiReturn') and o.name = 'FK_PSIRETUR_REFERENCE_PSISALES')
alter table PsiReturn
   drop constraint FK_PSIRETUR_REFERENCE_PSISALES
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PsiSalesRecord') and o.name = 'FK_PSISALES_REFERENCE_PSIINFOR')
alter table PsiSalesRecord
   drop constraint FK_PSISALES_REFERENCE_PSIINFOR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PsiTrade') and o.name = 'FK_PSITRADE_REFERENCE_PSIINFOR')
alter table PsiTrade
   drop constraint FK_PSITRADE_REFERENCE_PSIINFOR
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Inventory')
            and   type = 'U')
   drop table Inventory
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PsiAccess')
            and   type = 'U')
   drop table PsiAccess
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"PsiAfter-sales"')
            and   type = 'U')
   drop table "PsiAfter-sales"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PsiInformation')
            and   type = 'U')
   drop table PsiInformation
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PsiInventory')
            and   type = 'U')
   drop table PsiInventory
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PsiProcurement')
            and   type = 'U')
   drop table PsiProcurement
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PsiProduct')
            and   type = 'U')
   drop table PsiProduct
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PsiReturn')
            and   type = 'U')
   drop table PsiReturn
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PsiSalesRecord')
            and   type = 'U')
   drop table PsiSalesRecord
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PsiTrade')
            and   type = 'U')
   drop table PsiTrade
go

/*==============================================================*/
/* Table: Inventory                                             */
/*==============================================================*/
create table Inventory (
   PsiSerial            int                  identity(1,1) not for replication,
   PsilibraryId         int                  null,
   PsiProductId         int                  not null,
   PsiProductName       varchar(30)          not null,
   PsiProductNumber     varchar(30)          not null,
   PsiInventorytime     smalldatetime        not null,
   constraint PK_INVENTORY primary key (PsiSerial)
)
go

/*==============================================================*/
/* Table: PsiAccess                                             */
/*==============================================================*/
create table PsiAccess (
   PsiProductId         int                  identity(1,1) not for replication,
   PsiSerial            int                  null,
   PsiProductName       varchar(30)          not null,
   PsiProductNumber     varchar(30)          not null,
   PsiAccessTime        smalldatetime        not null,
   constraint PK_PSIACCESS primary key (PsiProductId)
)
go

/*==============================================================*/
/* Table: "PsiAfter-sales"                                      */
/*==============================================================*/
create table "PsiAfter-sales" (
   PsiProductId         int                  not null,
   PsiProductName       varchar(30)          not null,
   PsiProductEvaluate   varchar(200)         not null,
   PsiProductSatisfy    varchar(200)         not null,
   constraint "PK_PSIAFTER-SALES" primary key (PsiProductId)
)
go

/*==============================================================*/
/* Table: PsiInformation                                        */
/*==============================================================*/
create table PsiInformation (
   PsiUserId            int                  identity(1,1) not for replication,
   PsiUserName          varchar(30)          not null,
   PsiUserPwd           varchar(20)          not null,
   PsiBalance           money                not null,
   constraint PK_PSIINFORMATION primary key (PsiUserId)
)
go

/*==============================================================*/
/* Table: PsiInventory                                          */
/*==============================================================*/
create table PsiInventory (
   PsilibraryId         int                  identity(1,1) not for replication,
   PsiUserId            int                  null,
   PsilibraryName       varchar(30)          not null,
   PsilibraryNumber     varchar(30)          not null,
   PsilibraryInTime     smalldatetime        not null,
   PsilibraryOutTime    smalldatetime        not null,
   constraint PK_PSIINVENTORY primary key (PsilibraryId)
)
go

/*==============================================================*/
/* Table: PsiProcurement                                        */
/*==============================================================*/
create table PsiProcurement (
   PsiProductId         int                  identity(1,1) not for replication,
   PsiUserId            int                  null,
   PsiProductName       varchar(30)          not null,
   PsiProductPrice      money                not null,
   PsiProductNumber     varchar(30)          not null,
   PsiProductTime       smalldatetime        not null,
   constraint PK_PSIPROCUREMENT primary key (PsiProductId)
)
go

/*==============================================================*/
/* Table: PsiProduct                                            */
/*==============================================================*/
create table PsiProduct (
   PsiProductId         int                  not null,
   PsiUserId            int                  null,
   PsiProductName       varchar(30)          not null,
   PsiProductPrice      money                not null,
   PsiProductSeller     varchar(30)          not null,
   PsiShelftTime        smalldatetime        not null,
   constraint PK_PSIPRODUCT primary key (PsiProductId)
)
go

/*==============================================================*/
/* Table: PsiReturn                                             */
/*==============================================================*/
create table PsiReturn (
   PsiProductId         int                  not null,
   PsiProductName       varchar(30)          not null,
   PsiProductRefund     varchar(200)         not null,
   PsiProductTime       smalldatetime        not null,
   constraint PK_PSIRETURN primary key (PsiProductId)
)
go

/*==============================================================*/
/* Table: PsiSalesRecord                                        */
/*==============================================================*/
create table PsiSalesRecord (
   PsiProductId         int                  identity(1,1) not for replication,
   PsiUserId            int                  null,
   PsiProductName       varchar(30)          not null,
   PsiProductIdPrice    money                not null,
   PsiProductNumber     varchar(30)          not null,
   PsiProductTime       smalldatetime        not null,
   constraint PK_PSISALESRECORD primary key (PsiProductId)
)
go

/*==============================================================*/
/* Table: PsiTrade                                              */
/*==============================================================*/
create table PsiTrade (
   PsiTradingId         int                  identity(1,1) not for replication,
   PsiUserId            int                  null,
   PsiByUser            varchar(30)          not null,
   PsiNumber            varchar(30)          not null,
   PsiAmount            money                not null,
   PsiTradingTime       smalldatetime        not null,
   constraint PK_PSITRADE primary key (PsiTradingId)
)
go

alter table Inventory
   add constraint FK_INVENTOR_REFERENCE_PSIINVEN foreign key (PsilibraryId)
      references PsiInventory (PsilibraryId)
go

alter table PsiAccess
   add constraint FK_PSIACCES_REFERENCE_INVENTOR foreign key (PsiSerial)
      references Inventory (PsiSerial)
go

alter table "PsiAfter-sales"
   add constraint FK_PSIAFTER_REFERENCE_PSISALES foreign key (PsiProductId)
      references PsiSalesRecord (PsiProductId)
go

alter table PsiInventory
   add constraint FK_PSIINVEN_REFERENCE_PSIINFOR foreign key (PsiUserId)
      references PsiInformation (PsiUserId)
go

alter table PsiProcurement
   add constraint FK_PSIPROCU_REFERENCE_PSIINFOR foreign key (PsiUserId)
      references PsiInformation (PsiUserId)
go

alter table PsiProduct
   add constraint FK_PSIPRODU_REFERENCE_PSIINFOR foreign key (PsiUserId)
      references PsiInformation (PsiUserId)
go

alter table PsiReturn
   add constraint FK_PSIRETUR_REFERENCE_PSISALES foreign key (PsiProductId)
      references PsiSalesRecord (PsiProductId)
go

alter table PsiSalesRecord
   add constraint FK_PSISALES_REFERENCE_PSIINFOR foreign key (PsiUserId)
      references PsiInformation (PsiUserId)
go

alter table PsiTrade
   add constraint FK_PSITRADE_REFERENCE_PSIINFOR foreign key (PsiUserId)
      references PsiInformation (PsiUserId)
go
